#! /bin/bash
HIVE_HOME=/usr/bin/hive
Last_DATE=$(date -d "-1 day" +%Y-%m-%d)
${HIVE_HOME} -S -e "
SET hive.auto.convert.join=false;
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=10000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--矢量优化
SET hive.vectorized.execution.enabled=true;
--关联优化
SET hive.optimize.correlation=true;
--读取零拷贝
SET hive.exec.orc.zerocopy=true;
SET hive.optimize.skewjoin=true;
SET hive.optimize.skewjoin.complietime=true;
SET hive.uptimize.union.remove=true;
--SET hive.groupby.skewindata=true;
INSERT INTO zz15_dws_renxinlei.attendance_summary PARTITION(yearinfo,monthinfo,dayinfo)
SELECT 
    ctu.class_date AS dateinfo,
    ctu.class_id,
    css.studying_student_count,
    ca.morning_att_count,
    concat(round(nvl(ca.morning_att_count,0) / css.studying_student_count * 100,2),'%') AS morning_att_ratio,
    ca.afternoon_att_count,
    concat(round(nvl(ca.afternoon_att_count,0) / css.studying_student_count * 100,2),'%') AS afternoon_att_ratio,
    ca.evening_att_count,
    concat(round(nvl(ca.evening_att_count,0) / css.studying_student_count * 100,2),'%') AS evening_att_ratio,
    ca.morning_late_count,
    concat(round(nvl(ca.morning_late_count,0) / css.studying_student_count * 100,2),'%') AS morning_late_ratio,
    ca.afternoon_late_count,
    concat(round(nvl(ca.afternoon_late_count,0) / css.studying_student_count * 100,2),'%') AS afternoon_late_ratio,
    ca.evening_late_count,
    concat(round(nvl(ca.evening_late_count,0) / css.studying_student_count * 100,2),'%') AS evening_late_ratio,
    cl.morning_leave_count,
    concat(round(nvl(cl.morning_leave_count,0) / css.studying_student_count * 100,2),'%') AS morning_leave_ratio,
    cl.afternoon_leave_count,
    concat(round(nvl(cl.afternoon_leave_count,0) / css.studying_student_count * 100,2),'%') AS afternoon_leave_ratio,
    cl.evening_leave_count,
    concat(round(nvl(cl.evening_leave_count,0) / css.studying_student_count * 100,2),'%') AS evening_leave_ratio,
    ct.morning_truant_count,
    concat(round(nvl(ct.morning_truant_count,0) / css.studying_student_count * 100,2),'%') AS morning_truant_ratio,
    ct.afternoon_truant_count,
    concat(round(nvl(ct.afternoon_truant_count,0) / css.studying_student_count * 100,2),'%') AS afternoon_truant_ratio,
    ct.evening_truant_count,
    concat(round(nvl(ct.evening_truant_count,0) / css.studying_student_count * 100,2),'%') AS evening_truant_ratio,
    substr(ctu.class_date,1,4) AS yearinfo,
    substr(ctu.class_date,6,2) AS monthinfo,
    substr(ctu.class_date,9,2) AS dayinfo
FROM (SELECT * FROM itcast_ods.course_table_upload_detail WHERE nvl(content,'')!='' AND content != '开班典礼' AND dt = ${Last_DATE}) AS ctu
    LEFT JOIN itcast_ods.class_studying_student_count AS css ON css.class_id = ctu.class_id AND css.studying_date = ctu.class_date
    LEFT JOIN zz15_dws_renxinlei.class_attendance AS ca ON ca.class_id = ctu.class_id AND ca.dateinfo = ctu.class_date
    LEFT JOIN zz15_dws_renxinlei.class_leave AS cl ON cl.class_id = ctu.class_id AND cl.dateinfo = ctu.class_date
    LEFT JOIN zz15_dws_renxinlei.class_truant AS ct ON ct.class_id = ctu.class_id AND ct.dateinfo = ctu.class_date;